package com.sap.jcp.sm.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.sap.jcp.sm.domain.Config;
import com.sap.jcp.sm.dto.UserTimelineDTO;
import com.sap.jcp.sm.dto.UserTimelineGridDTO;
import com.sap.jcp.sm.dto.UserTimelineRowDTO;

public class ExcelExportUtil {
	Workbook workBook = null;
	FileOutputStream fileOut = null;
	Sheet weekEndWorksheet = null;
	Sheet wilyWorksheet = null;
	Sheet weeklyWorksheet = null;

	private void createExcelSheet(String weekId, String path) {
		
		try {
			InputStream inp = new FileInputStream(path + "/schedule_tmp.xlsx");
		    workBook = WorkbookFactory.create(inp);
		    weekEndWorksheet = workBook.getSheetAt(0);
		    wilyWorksheet = workBook.getSheetAt(1);
		    weeklyWorksheet = workBook.getSheetAt(2);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
	}

	public void createExcel(UserTimelineDTO userTimelineDTO,
			String realContextPath, Config config, int howManyDays) {
		if (userTimelineDTO != null) {
			    createExcelSheet(userTimelineDTO.getCurrentWeekId(), realContextPath);
				createHeader(weekEndWorksheet, userTimelineDTO, howManyDays);
				for (int index = 0; index < 7; index++) {
					Row row3 = weeklyWorksheet.getRow(3);
					Cell cellI4 = row3.getCell((short) index + 3);
					cellI4.setCellValue(userTimelineDTO.getNextSevenDaysList().get(index));
				}
				int userCount = 0;
				int userCount1 = 0;
			for (UserTimelineRowDTO utlrdto : userTimelineDTO.getUserTimelineRowList()) {
				//System.out.println(utlrdto.getUserName() + " : ");
				userCount =	addUserDetails(weekEndWorksheet, userCount, utlrdto.getPrimaryTrack(), utlrdto.getUserName(),  utlrdto.getUserTimelineGridList(), config);
				userCount1 = addUserDetailsForWeekly(weeklyWorksheet, userCount1, utlrdto.getUserTimelineGridList().get(0).getTrackName(), utlrdto.getUserName(),  utlrdto.getUserTimelineGridList(), config);
				int index = 0;
				for (UserTimelineGridDTO userTimelineGridDTO : utlrdto.getUserTimelineGridList()) {
					if (config.getFirstShiftId().equals(userTimelineGridDTO.getShiftId()) && config.getShiftMastersTrack().equals(userTimelineGridDTO.getTrackId())) {
						addShiftName(wilyWorksheet, utlrdto.getUserName(), 0, index);
					} if (config.getSecondShiftId().equals(userTimelineGridDTO.getShiftId())  && config.getShiftMastersTrack().equals(userTimelineGridDTO.getTrackId())) {
						addShiftName(wilyWorksheet, utlrdto.getUserName(), 2, index);
					}
					index++;
					
				}
				
			}
				
			
			try {
				fileOut = new FileOutputStream(realContextPath + "schedule.xlsx");
				workBook.write(fileOut);
				fileOut.flush();
				fileOut.close();
				//System.out.println(realContextPath + "schedule.xlsx");
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	private String preTrack1 = "";
	short color1 = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;

	private int addUserDetailsForWeekly(Sheet weeklyWorksheet2, int number, String track, String user,
			List<UserTimelineGridDTO> userTimelineGridList, Config config) {
		CellStyle cellStyle = workBook.createCellStyle();
		CellStyle schduleStyle = workBook.createCellStyle();
		CellStyle blankStyle = workBook.createCellStyle();
		CellStyle nameStyle = workBook.createCellStyle();
		
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		  
		HSSFColor myColor  = new HSSFColor();
		if (preTrack1.equals(userTimelineGridList.get(0).getUserId())) {
			cellStyle.setFillForegroundColor(color);
		} else {
			if (color1 == HSSFColor.GREY_40_PERCENT.index) {
				color1 = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
				cellStyle.setFillForegroundColor(color1);
			} else {
				cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
				color1 = HSSFColor.GREY_40_PERCENT.index;
			}
		}
		preTrack1 = userTimelineGridList.get(0).getUserId();
		cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		schduleStyle.cloneStyleFrom(cellStyle);
		schduleStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
		schduleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		schduleStyle.setBorderBottom(CellStyle.BORDER_THIN);
		
		int dayCount = 0;
		Set<String> set = new HashSet<String>();
		set.add(track);
		for (UserTimelineGridDTO userTimelineGridDTO : userTimelineGridList) {
			if (!track.equalsIgnoreCase(userTimelineGridDTO.getTrackName())){
				set.add(userTimelineGridDTO.getTrackName());
			}
		}
		Map<String, Row> map = new HashMap<String, Row>();
		if (set.size() > 0) {
			int setIndex = 0;
			for (String trackString : set) {
				map.put(trackString, weeklyWorksheet2.getRow(number + setIndex + 4));
				Row trackRow =weeklyWorksheet2.getRow(number + setIndex + 4);
				Cell cell12 = trackRow.getCell((short) 0);
				cell12.setCellValue(number + 1);
				cell12.setCellStyle(cellStyle);
				Cell cell32 = trackRow.getCell((short) 2);
				cell32.setCellValue(trackString);
				cell32.setCellStyle(cellStyle);
				nameStyle.cloneStyleFrom(cellStyle);
				Cell cell2 = trackRow.getCell((short) 1);
				cell2.setCellValue(user);
				nameStyle.setAlignment(CellStyle.ALIGN_LEFT);
				cell2.setCellStyle(nameStyle);
				number++;
			}
		}
		for (UserTimelineGridDTO userTimelineGridDTO : userTimelineGridList) {
			Row row1 = map.get(userTimelineGridDTO.getTrackName());
			Cell cell4 = row1.getCell((short) 3 + dayCount++);
			cell4.setCellValue(userTimelineGridDTO.getShiftName());
			blankStyle.cloneStyleFrom(schduleStyle);
			blankStyle.setFillForegroundColor(HSSFColor.WHITE.index);
			blankStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			if (userTimelineGridDTO.getShiftName() != null && userTimelineGridDTO.getShiftName().length() > 0 && !userTimelineGridDTO.getShiftId().equalsIgnoreCase(config.getDefaultShift())) {
				cell4.setCellStyle(schduleStyle);
			} else {
				cell4.setCellStyle(blankStyle);
			}
		}
		return number;
	}
	private void addShiftName(Sheet workSheet, String userName, int i, int index) {
		Row row3 = workSheet.getRow(3 + i);
		Cell cellI4 = row3.getCell((short) index + 2);
		cellI4.setCellValue(userName);
	}
	private String preTrack = "";
	short color = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
	private int addUserDetails(Sheet weeklyWorksheet2, int number, String track, String user,
			List<UserTimelineGridDTO> userTimelineGridList, Config config) {
		CellStyle cellStyle = workBook.createCellStyle();
		CellStyle schduleStyle = workBook.createCellStyle();
		CellStyle blankStyle = workBook.createCellStyle();
		CellStyle nameStyle = workBook.createCellStyle();
		
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		  
		HSSFColor myColor  = new HSSFColor();
		if (preTrack.equals(userTimelineGridList.get(0).getUserId())) {
			cellStyle.setFillForegroundColor(color);
		} else {
			if (color == HSSFColor.GREY_40_PERCENT.index) {
				color = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
				cellStyle.setFillForegroundColor(color);
			} else {
				cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
				color = HSSFColor.GREY_40_PERCENT.index;
			}
		}
		preTrack = userTimelineGridList.get(0).getUserId();
		cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		
		schduleStyle.cloneStyleFrom(cellStyle);
		schduleStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
		schduleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		schduleStyle.setBorderBottom(CellStyle.BORDER_THIN);
		
		int dayCount = 0;
		Set<String> set = new HashSet<String>();
		if (!(userTimelineGridList.get(0).getTrackId().equalsIgnoreCase(track) && userTimelineGridList.get(0).getShiftId().equalsIgnoreCase(config.getDefaultShift()))) {
			set.add(userTimelineGridList.get(0).getTrackName());
		}
		if (!(userTimelineGridList.get(1).getTrackId().equalsIgnoreCase(track) && userTimelineGridList.get(1).getShiftId().equalsIgnoreCase(config.getDefaultShift()))) {
			set.add(userTimelineGridList.get(1).getTrackName());
		}
		Map<String, Row> map = new HashMap<String, Row>();
		if (set.size() > 0) {
			int setIndex = 0;
			for (String trackString : set) {
				map.put(trackString, weeklyWorksheet2.getRow(number + setIndex + 4));
				Row trackRow =weeklyWorksheet2.getRow(number + setIndex + 4);
				Cell cell12 = trackRow.getCell((short) 0);
				cell12.setCellValue(number + 1);
				cell12.setCellStyle(cellStyle);
				Cell cell32 = trackRow.getCell((short) 2);
				cell32.setCellValue(trackString);
				cell32.setCellStyle(cellStyle);
				nameStyle.cloneStyleFrom(cellStyle);
				Cell cell2 = trackRow.getCell((short) 1);
				cell2.setCellValue(user);
				nameStyle.setAlignment(CellStyle.ALIGN_LEFT);
				cell2.setCellStyle(nameStyle);
				number++;
			}
		}
		for (UserTimelineGridDTO userTimelineGridDTO : userTimelineGridList) {
			if (dayCount > 1) {
				break;
			}
			if (map.get(userTimelineGridDTO.getTrackName()) == null) {
			dayCount++;
				continue;
			}
			Row row1 = map.get(userTimelineGridDTO.getTrackName());
			Cell cell4 = row1.getCell((short) 3 + dayCount++);

			String shift1 =  "";
			if (!config.getDefaultShift().equals( userTimelineGridDTO.getShiftId())){
				if (config.getFirstShiftId().equals(userTimelineGridDTO.getShiftId())) {
					shift1 = "1";
				} else if (config.getSecondShiftId().equals(userTimelineGridDTO.getShiftId())) {
					shift1 = "2";
				} else {
					shift1 = userTimelineGridDTO.getShiftName();
				}
			}
			cell4.setCellValue(shift1);
			blankStyle.cloneStyleFrom(schduleStyle);
			blankStyle.setFillForegroundColor(HSSFColor.WHITE.index);
			blankStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			if (userTimelineGridDTO.getShiftName() != null && userTimelineGridDTO.getShiftName().length() > 0 && !userTimelineGridDTO.getShiftId().equalsIgnoreCase(config.getDefaultShift())) {
				cell4.setCellStyle(schduleStyle);
			} else {
				cell4.setCellStyle(blankStyle);
			}
		}
		return number;
	}
	
	private void createHeader(Sheet workSheet, UserTimelineDTO userTimelineDTO, int howManyDays) {
		Row row1 = workSheet.getRow(0);
		Cell cellB1 = row1.getCell((short) 1);
		CellStyle cs = cellB1.getCellStyle();
		String headerText = "Week End Support[";
		for (int index = 0; index < howManyDays; index++) {
			Row row3 = workSheet.getRow(3);
			Cell cellD4 = row3.getCell((short) index + 3);
			cellD4.setCellValue(userTimelineDTO.getNextSevenDaysList().get(index));
			headerText += userTimelineDTO.getNextSevenDaysList().get(index);
			headerText +=", ";
			
			Cell cellI4 = row3.getCell((short) index + 3);
			cellI4.setCellValue(userTimelineDTO.getNextSevenDaysList().get(index));
		}
		headerText +="]";
		cellB1.setCellValue(headerText);
		Font f = workBook.createFont();
		f.setBoldweight(Font.BOLDWEIGHT_BOLD);
		f.setFontName("Georgia");
		cs.setFont(f);
		cellB1.setCellStyle(cs);
	}

}